# -*- coding: utf-8 -*-

from datetime import datetime

from sqlalchemy import *

from sqlalchemy.orm import *

import time, random, md5

import os
import sys

from time import time, localtime, strftime


#for $tab in $tables
"""
class: $tab.capitalize()
"""
class $tab.capitalize()(object):
    """
    
    """
    def __init__(self):
        pass

#end for

class DB:

    def __init__(self, uri, echo = False):
        print "create engine"
        self.err_count = 0
        ####uri = 'sqlite:///data.dat'
        ###uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/mymodel?charset=utf8&use_unicode=0'
        self.engine = create_engine(uri,encoding='utf-8')
        metadata = MetaData(self.engine)
        metadata.bind.echo  = echo

#for $tab in $tables
###$tab.lower()
#set $ltab = $tab.lower()
        
        """
        taqble: ${ltab}
        ForeignKey('users.user_id')
        """
        self.${ltab} = Table('${ltab}', metadata,
#set one = $tables[$tab]
#set colslen = len(one)
###$one
#for $row in range(1,$colslen+1)
#set $col = $tables[$tab][$row]
###$col.name
    #if $col.is_pk == 1
            Column('${col.name}', ${col.data_type}, primary_key=True, autoincrement=True, nullable=${col.nullable}), ###default=${col.default}
    #else
            Column('${col.name}', ${col.data_type}, nullable=${col.nullable} ${col.default_value}), ####${col.nullable}
    #end if
#end for
            Column('project', String(36), nullable=False, default='kylin')
        )
#end for
        metadata.create_all()
        
        """
        mapper
        """
#for $tab in $tables
        mapper($tab.capitalize(),self.$tab.lower())
#end for

    def execute(self, sql):
        sql = sql.decode('GBK')
        connection = self.engine.connect()
        try:
            result = connection.execute(sql)
            return result
        except Exception, e:
            self.err_count = self.err_count + 1
            print sql.encode('utf8')
            print e
            if self.err_count > 6:
                sys.exit(0)
    
    def exists(self):
        sql = "select name from projects where name = 'kylin'"
        result = self.execute(sql)
        value = result.fetchone()
        if value != None:
            if value[0] == 'kylin':
                print "project already exists"
                sys.exit(0)
        
    def insert(self):
        now = "'%s'" %(strftime("%Y-%m-%d %H:%M:%S", localtime()))
        sql = "insert into projects (name, createdby, createdon, active, ordering, hits, project) values('kylin', 'admin', %s, 1,1,0,'kylin')" %now
        self.execute(sql)

    def source(self, source):
        file = open(source,'r')    
        i = 0
        sqlp = []
        j = 0
        for line in file:
          i = i +1
          line = line.strip()
          now = strftime("%Y-%m-%d %H:%M:%S", localtime())
          now = "'%s'" % now

          line = line.replace('now()',now)
          if line[-2:] ==');':
              sqlp.append(line)
              mysql = ''
              for sql in sqlp:
                  mysql += sql
              print mysql #.decode('utf8')
              j = j +1
              if j >3:
                  pass
              self.execute(mysql)
              #db.execute('PRAGMA table_info("models")')
              print "\n-----------\n"
              sqlp = []
              
              #print line, i
          else:
            sqlp.append(line)

    def select(self):        
        s=self.projects.select(self.projects.c.name == 'kylin')
        print s
        r = s.execute()
        print r.fetchone()
        pass

    def buildfk2(self):
        
        """
        select pk_table_id from tables
        id = 
        update columns set fk_table_id = id
        
        """
        pass
        
    def buildfk(self):
        sql = """
            UPDATE COLUMNS col SET col.fk_table_id = ( SELECT tab.pk_table_id
            FROM TABLES tab
            WHERE tab.name = col.tablename ) ;
            """
        print sql
        
def test():
    uri = 'sqlite:///model.db3'
    #uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/mymodel?charset=utf8&use_unicode=0'

    db = DB(uri, False)
    db.exists()    
    db.source('metadata.sql')
    db.insert()

def getMeta():
    #uri = 'sqlite:///data.dat'
    uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/mymodel?charset=utf8&use_unicode=0'
    db = DB(uri, False)
    db.select()

if __name__ == "__main__":
    test()
    #getMeta()
